using System;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Collections.Generic;
using Dapper;
using MediatR;
using Microsoft.Extensions.Logging;
using RBAC.Interstructrue.Base;
using RBAC.Read.Api.Application.Command.ArrivalInspection;
using RBAC.Read.Api.Application.Dto.Quality;

namespace RBAC.Read.Api.Application.Handler.ArrivalInspection
{
    /// <summary>
    /// 导出到货检验为 CSV。
    /// - 若指定 Id：导出该单据的明细（含部分单据头字段）。
    /// - 未指定 Id：导出列表（每行一个单据，含汇总字段）。
    /// </summary>
    public class ExportArrivalInspectionHandler : IRequestHandler<ExportArrivalInspectionCommand, byte[]>
    {
        private readonly IDbConnectionFactory _connectionFactory;
        private readonly ILogger<ExportArrivalInspectionHandler> _logger;

        public ExportArrivalInspectionHandler(IDbConnectionFactory connectionFactory, ILogger<ExportArrivalInspectionHandler> logger)
        {
            _connectionFactory = connectionFactory;
            _logger = logger;
        }

        public async Task<byte[]> Handle(ExportArrivalInspectionCommand request, CancellationToken cancellationToken)
        {
            using var conn = _connectionFactory.CreateConnectionWithRetry();

            // 构建 where 过滤与列表一致
            var where = new StringBuilder(" WHERE info.IsDeleted = 0 ");
            var p = new DynamicParameters();
            if (request.Id.HasValue && request.Id.Value > 0)
            {
                where.Append(" AND info.Id = @Id ");
                p.Add("Id", request.Id.Value);
            }
            if (!string.IsNullOrWhiteSpace(request.Subject))
            {
                where.Append(" AND info.Subject LIKE @Subject ");
                p.Add("Subject", $"%{request.Subject!.Trim()}%");
            }
            if (!string.IsNullOrWhiteSpace(request.DocCode))
            {
                where.Append(" AND info.DocCode LIKE @DocCode ");
                p.Add("DocCode", $"%{request.DocCode!.Trim()}%");
            }
            if (!string.IsNullOrWhiteSpace(request.Inspector))
            {
                where.Append(" AND info.Inspector LIKE @Inspector ");
                p.Add("Inspector", $"%{request.Inspector!.Trim()}%");
            }
            if (!string.IsNullOrWhiteSpace(request.PurchaseOrderNo))
            {
                where.Append(" AND info.PurchaseOrderNo LIKE @PurchaseOrderNo ");
                p.Add("PurchaseOrderNo", $"%{request.PurchaseOrderNo!.Trim()}%");
            }
            if (request.InspectionDateStart.HasValue)
            {
                where.Append(" AND info.InspectionDate >= @InspectionDateStart ");
                p.Add("InspectionDateStart", request.InspectionDateStart.Value.Date);
            }
            if (request.InspectionDateEnd.HasValue)
            {
                where.Append(" AND info.InspectionDate < @InspectionDateEnd ");
                p.Add("InspectionDateEnd", request.InspectionDateEnd.Value.Date.AddDays(1));
            }

            // 限制最多导出行数
            int maxRows = Math.Clamp(request.MaxRows ?? 10000, 1, 50000);

            // 如果传入 Id，则导出明细
            if (request.Id.HasValue && request.Id.Value > 0)
            {
                var headerSql = @"SELECT
    info.Id,
    info.DocCode,
    info.Subject,
    info.InspectionDate,
    info.Inspector,
    info.Department,
    info.PurchaseOrderNo,
    info.SupplierName,
    info.PurchaseDate,
    info.PurchaseContact,
    info.ContactPhone,
    info.Buyer,
    info.PurchaseDept,
    info.ProjectName
FROM ArrivalInspectionInfo info
" + where.ToString() + " ORDER BY info.Id DESC LIMIT 1";

                var header = await conn.QueryFirstOrDefaultAsync<ArrivalInspectionListDto>(headerSql, p);
                if (header == null)
                {
                    return Encoding.UTF8.GetBytes("无数据");
                }

                var itemsSql = @"SELECT Id, InspectionId, PurchaseOrderNo, MaterialCode, MaterialName, SpecModel, Unit, Brand,
       PurchaseQty, ReceivedQty, InspectionType, InspectQty, QualifiedQty, UnqualifiedQty, InspectResult, UnqualifiedReason, Remark
FROM ArrivalInspectionItem WHERE InspectionId = @HId LIMIT @MaxRows";
                var its = (await conn.QueryAsync<ArrivalInspectionItemDto>(itemsSql, new { HId = header.Id, MaxRows = maxRows })).ToList();

                var sb = new StringBuilder();
                // CSV 头
                var cols = new[] {
                    "检验单号","检验主题","检验日期","检验人员","部门",
                    "采购单号","供应商","采购日期","联系人","联系电话","采购人员","采购部门","项目",
                    "物品编码","物品名称","规格型号","单位","品牌",
                    "采购数量","收货数量","检验类型","检验数量","合格数量","不合格数","结论","不合格原因","备注"
                };
                sb.AppendLine(string.Join(',', cols.Select(EscapeCsv)));

                foreach (var it in its)
                {
                    var row = new object?[]
                    {
                        header.DocCode, header.Subject, header.InspectionDate.ToString("yyyy-MM-dd"), header.Inspector, header.Department,
                        header.PurchaseOrderNo, header.SupplierName, header.PurchaseDate?.ToString("yyyy-MM-dd"), header.PurchaseContact, header.ContactPhone, header.Buyer, header.PurchaseDept, header.ProjectName,
                        it.MaterialCode, it.MaterialName, it.SpecModel, it.Unit, it.Brand,
                        it.PurchaseQty, it.ReceivedQty, it.InspectionType, it.InspectQty, it.QualifiedQty, it.UnqualifiedQty, it.InspectResult, it.UnqualifiedReason, it.Remark
                    };
                    sb.AppendLine(string.Join(',', row.Select(EscapeCsv)));
                }

                return GetCsvBytes(sb.ToString());
            }
            else
            {
                // 未传 Id：导出列表（头信息+汇总列）
                var sql = @"SELECT
    info.Id,
    info.DocCode,
    info.Subject,
    info.InspectionDate,
    info.Inspector,
    info.Department,
    info.PurchaseOrderNo,
    info.SupplierName,
    info.PurchaseDate,
    info.PurchaseContact,
    info.ContactPhone,
    info.Buyer,
    info.PurchaseDept,
    info.ProjectName,
    info.CreateUser,
    info.CreateTime
FROM ArrivalInspectionInfo info
" + where.ToString() + " ORDER BY info.CreateTime DESC, info.Id DESC LIMIT @MaxRows";
                p.Add("MaxRows", maxRows);
                var headers = (await conn.QueryAsync<ArrivalInspectionListDto>(sql, p)).ToList();
                if (headers.Count == 0)
                {
                    return Encoding.UTF8.GetBytes("无数据");
                }

                // 查询聚合
                var ids = headers.Select(x => x.Id).ToList();
                var itemsAggSql = @"SELECT InspectionId, SUM(InspectQty) InspectQty, SUM(QualifiedQty) QualifiedQty, SUM(UnqualifiedQty) UnqualifiedQty, COUNT(1) ItemCount
FROM ArrivalInspectionItem WHERE InspectionId IN @Ids GROUP BY InspectionId";
                var aggs = await conn.QueryAsync<(long InspectionId, decimal InspectQty, decimal QualifiedQty, decimal UnqualifiedQty, int ItemCount)>(itemsAggSql, new { Ids = ids });
                var aggDict = aggs.ToDictionary(x => x.InspectionId, x => x);

                var sb = new StringBuilder();
                var cols = new[] {
                    "检验单号","检验主题","检验日期","检验人员","部门",
                    "采购单号","供应商","采购日期","联系人","联系电话","采购人员","采购部门","项目",
                    "明细条数","检验数量合计","合格数量合计","不合格数量合计",
                    "创建人","创建时间"
                };
                sb.AppendLine(string.Join(',', cols.Select(EscapeCsv)));
                foreach (var h in headers)
                {
                    aggDict.TryGetValue(h.Id, out var agg);
                    var row = new object?[]
                    {
                        h.DocCode, h.Subject, h.InspectionDate.ToString("yyyy-MM-dd"), h.Inspector, h.Department,
                        h.PurchaseOrderNo, h.SupplierName, h.PurchaseDate?.ToString("yyyy-MM-dd"), h.PurchaseContact, h.ContactPhone, h.Buyer, h.PurchaseDept, h.ProjectName,
                        agg.ItemCount, agg.InspectQty, agg.QualifiedQty, agg.UnqualifiedQty,
                        h.CreateUser, h.CreateTime.ToString("yyyy-MM-dd HH:mm:ss")
                    };
                    sb.AppendLine(string.Join(',', row.Select(EscapeCsv)));
                }
                return GetCsvBytes(sb.ToString());
            }
        }

        private static string EscapeCsv(object? value)
        {
            if (value == null) return "";
            var s = Convert.ToString(value);
            if (string.IsNullOrEmpty(s)) return "";
            // 若包含逗号、引号或换行，使用双引号包裹并转义引号
            bool needQuote = s.Contains(',') || s.Contains('"') || s.Contains('\n') || s.Contains('\r');
            if (needQuote)
            {
                s = s.Replace("\"", "\"\"");
                return $"\"{s}\"";
            }
            return s;
        }

        private static byte[] GetCsvBytes(string content)
        {
            // Prepend UTF-8 BOM to make Excel on Windows recognize UTF-8 properly
            var utf8 = new UTF8Encoding(encoderShouldEmitUTF8Identifier: true);
            return utf8.GetPreamble().Concat(utf8.GetBytes(content)).ToArray();
        }
    }
}
